from sqlalchemy import *
from migrate import *
from datetime import datetime
from migrate.changeset import *

meta = MetaData(migrate_engine)
DiveSite = Table('DiveSite', meta,
  Column('id', Integer, primary_key=True,autoincrement=True),
  Column('name', Unicode(120), nullable=False),
  Column('location', Unicode(120)),
  Column('latitude', Float ,index=True),
  Column('longitude', Float ,index=True),
  Column('altitude', Float),
  Column('timezone', SmallInteger),
  Column('arealength', Float),
  Column('areawidth', Float),
  Column('averagevisibility', Float),
  Column('bottom', Unicode(120)),
  Column('density', Float),
  Column('difficulty_id', Integer,ForeignKey('Difficulty.id')),
  Column('globallightintensity_id', Integer,ForeignKey('GlobalLightIntensity.id')),
  Column('maximumdepth', Float),
  Column('maximumvisibility', Float),
  Column('minimumdepth', Float),
  Column('minimumvisibility', Float),
  Column('rating', SmallInteger),
  Column('terrain', Unicode(250)),
  Column('address_id', Integer,ForeignKey('Address.id')),
)


DiveSiteComment = Table('DiveSiteComment', meta,
    Column('id',Integer, primary_key=True,autoincrement=True),
    Column('rating',SmallInteger),
    Column('text',Unicode(250)),
    Column('user_id',Integer ,ForeignKey('tg_user.user_id')),
    Column('diveSite_id',Integer ,ForeignKey('DiveSite.id')),
)
tg_user = Table('tg_user', meta,
  Column('user_id', Integer, autoincrement=True, primary_key=True),
  Column('user_name', Unicode(16), unique=True, nullable=False),
  Column('email_address', Unicode(255), unique=True, nullable=False),
  Column('display_name', Unicode(255)),
  Column('password', Unicode(80)),
  Column('created', DateTime, default=datetime.now),
)

def upgrade():
    DiveSiteComment.create()
    c = DiveSite.c['rating']
    c.drop()
    d = Column('creator_id',Integer ,ForeignKey('tg_user.user_id'))
    DiveSite.append_column(d)
    d.create(DiveSite)
    
    
    
def downgrade():
    c = Column('rating', SmallInteger)
    DiveSite.append_column(c)
    c.create(DiveSite)
    d = Column('creator_id',Integer ,ForeignKey('tg_user.user_id'))
    DiveSite.append_column(d)
    d.drop()
    DiveSiteComment.drop()
    
